package com.psmz.iwi.docanalyzer.core.document.business;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.psmz.iwi.docanalyzer.core.database.DaoException;
import com.psmz.iwi.docanalyzer.core.document.entities.Document;
import com.psmz.iwi.docanalyzer.core.document.entities.DocumentType;
import com.psmz.iwi.docanalyzer.core.document.entities.NumberOfWordsClassificationGroup;
import com.psmz.iwi.docanalyzer.core.document.entities.NumberOfWordsGroupType;

/**
 * Dao obslugi dokumentow.
 * 
 * @author piotrs10
 * 
 */
public class DocumentDao {

	private Connection connection;

	public DocumentDao(Connection connection) {
		this.connection = connection;
	}

	public List<Document> getAllNotIndexedDocuments() throws DaoException {
		final String query = "SELECT  " + "  d.id AS d_id,  "
				+ "  d.doc_name AS d_doc_name,  " + "  d.path AS d_path,  "
				+ "  d.indexed AS d_indexed,  "
				+ "  d.doc_type AS d_doc_type,  "
				+ "  d.creation_date AS d_creation_date,  "
				+ "  d.indexing_date AS d_indexing_date,  "
				+ " d.number_of_words AS d_number_of_words " + "FROM  "
				+ "  document d  " + "WHERE  "
				+ "  d.indexed = 'F' ORDER BY d.id ";
		List<Document> result = new ArrayList<Document>();
		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				Document document = extractDocument(rs);
				result.add(document);
			}
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			closePreparedStatement(preparedStatement);
		}
		return result;
	}

	public void save(Document document) throws DaoException {
		final String insert = "INSERT INTO document(doc_name, path, indexed, doc_type, creation_date, indexing_date, number_of_words) VALUES (?, ?, ?, ?, ?, ?, ?)";
		final String update = "UPDATE document  " + "  SET doc_name = ?,  "
				+ "  path = ?,  " + "   indexed = ?,  " + "   doc_type = ?,  "
				+ "   creation_date = ?,  " + "   indexing_date = ?,  "
				+ " number_of_words = ? " + "  WHERE ID = ?";
		PreparedStatement preparedStatement = null;
		try {
			if (document.getId() != null) {
				// update
				preparedStatement = connection.prepareStatement(update);
				preparedStatement.setString(1, document.getName());
				preparedStatement.setString(2, document.getFile()
						.getAbsolutePath());
				preparedStatement
						.setString(3, document.isIndexed() ? "T" : "F");
				preparedStatement.setString(4, document.getType().getCode());
				preparedStatement.setDate(5, new java.sql.Date(document
						.getCreationDate().getTime()));
				if (document.getIndexingDate() != null) {
					preparedStatement.setDate(6, new java.sql.Date(document
							.getIndexingDate().getTime()));
				} else {
					preparedStatement.setNull(6, java.sql.Types.DATE);
				}
				if (document.getNumberOfWords() != null) {
					preparedStatement.setLong(7, document.getNumberOfWords());
				} else {
					preparedStatement.setNull(7, java.sql.Types.NUMERIC);
				}
				preparedStatement.setLong(8, document.getId());
				preparedStatement.executeUpdate();
			} else {
				// insert
				preparedStatement = connection.prepareStatement(insert);
				preparedStatement.setString(1, document.getName());
				preparedStatement.setString(2, document.getFile()
						.getAbsolutePath());
				preparedStatement
						.setString(3, document.isIndexed() ? "T" : "F");
				preparedStatement.setString(4, document.getType().getCode());
				preparedStatement.setDate(5, new java.sql.Date(document
						.getCreationDate().getTime()));
				if (document.getIndexingDate() != null) {
					preparedStatement.setDate(6, new java.sql.Date(document
							.getIndexingDate().getTime()));
				} else {
					preparedStatement.setNull(6, java.sql.Types.DATE);
				}
				preparedStatement.executeUpdate();
			}
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			closePreparedStatement(preparedStatement);
		}
	}

	public Document getDocumentById(long id) throws DaoException {
		final String query = "SELECT  " + "  d.id AS d_id,  "
				+ "  d.doc_name AS d_doc_name,  " + "  d.path AS d_path,  "
				+ "  d.indexed AS d_indexed,  "
				+ "  d.doc_type AS d_doc_type,  "
				+ "  d.creation_date AS d_creation_date,  "
				+ "  d.indexing_date AS d_indexing_date,  "
				+ " d.number_of_words AS d_number_of_words " + "FROM  "
				+ "  document d  " + "WHERE  " + "  d.id = ?";
		PreparedStatement preparedStatement = null;
		Document document = null;
		try {
			preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				document = extractDocument(rs);
			}
		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			closePreparedStatement(preparedStatement);
		}
		return document;

	}

	public List<NumberOfWordsClassificationGroup> getClassifiedDocuments()
			throws DaoException {
		final String query = "SELECT  "
				+ "  d.id AS d_id,    "
				+ "  d.doc_name AS d_doc_name,  "
				+ "  d.path AS d_path,    "
				+ "  d.indexed AS d_indexed,    "
				+ "  d.doc_type AS d_doc_type,  "
				+ "  d.creation_date AS d_creation_date,    "
				+ "  d.indexing_date AS d_indexing_date,    "
				+ "  d.number_of_words AS d_number_of_words,  "
				+ "  CASE  "
				+ "    WHEN d.number_of_words BETWEEN 0 AND 500 THEN 'SMALL'  "
				+ "	WHEN d.number_of_words BETWEEN 5001 AND 2000 THEN 'MEDIUM'  "
				+ "	ELSE 'BIG'  " + "  END AS d_now_group  " + "FROM    "
				+ "  document d  " + "WHERE    "
				+ "  d.number_of_words IS NOT NULL  " + "ORDER BY  "
				+ "  d_now_group ";
		List<NumberOfWordsClassificationGroup> groups = new ArrayList<NumberOfWordsClassificationGroup>();
		PreparedStatement preparedStatement = null;
		NumberOfWordsClassificationGroup currentGroup = null;
		try {
			preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			while (rs.next()) {
				Document document = extractDocument(rs);
				String groupName = rs.getString("d_now_group");
				NumberOfWordsGroupType numberOfWordsGroupType = NumberOfWordsGroupType
						.getByCode(groupName);
				if (currentGroup == null) {
					currentGroup = new NumberOfWordsClassificationGroup(
							numberOfWordsGroupType);
					currentGroup.addDocument(document);
				} else if (currentGroup.getGroupType().equals(
						numberOfWordsGroupType)) {
					currentGroup.addDocument(document);

				} else if (!currentGroup.getGroupType().equals(
						numberOfWordsGroupType)) {
					groups.add(currentGroup);
					NumberOfWordsClassificationGroup newGroup = new NumberOfWordsClassificationGroup(
							numberOfWordsGroupType);
					newGroup.addDocument(document);
					currentGroup = newGroup;
				}
			}
			if (currentGroup != null) {
				groups.add(currentGroup);
			}

		} catch (SQLException e) {
			throw new DaoException(e);
		} finally {
			closePreparedStatement(preparedStatement);
		}
		return groups;
	}

	private void closePreparedStatement(PreparedStatement preparedStatement) {
		if (preparedStatement != null) {
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				System.err.println("Blad zamykania prepared statement");
				System.err.println(e.toString());
			}
		}
	}

	private Document extractDocument(ResultSet rs) throws SQLException {
		Document document = new Document();
		document.setId(rs.getLong("d_id"));
		document.setName(rs.getString("d_doc_name"));
		document.setFile(new File(rs.getString("d_path")));
		document.setIndexed("T".equals(rs.getString("d_indexed")));
		document.setType(DocumentType.getByCode(rs.getString("d_doc_type")));
		document.setCreationDate(rs.getDate("d_creation_date"));
		document.setIndexingDate(rs.getDate("d_indexing_date"));
		long numberOfWords = rs.getLong("d_number_of_words");
		if (rs.wasNull()) {
			document.setNumberOfWords(null);
		} else {
			document.setNumberOfWords(numberOfWords);
		}
		return document;
	}

}
